package com.citytransport.prokopjuk.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Statement;
//import org.apache.log4j.BasicConfigurator;
import org.apache.log4j.Logger;

import com.citytransport.prokopjuk.connectDB.ConnectionPool;
import com.citytransport.prokopjuk.route.Station;

public class DAOStation implements IDAOStation {
    ConnectionPool pool = ConnectionPool.getConnectionPool();
    static Logger logger = Logger.getLogger(DAOStation.class.getName());
    
    public void create(Station station) {
	PreparedStatement prepStmt = null;
	Connection conn = pool.getConnection();
	try {
	    prepStmt = conn.prepareStatement("INSERT INTO STATION "
		    + "(STATIONID,NAME,EXISTINGRAIL ,EXISTINGCABLE )"
		    + "VALUES(?,?,?,?)");

	    prepStmt.setInt(1, station.getStationID());
	    prepStmt.setString(2, station.getName());
	    prepStmt.setBoolean(3, station.isExistingRail());
	    prepStmt.setBoolean(4, station.isExistingCable());

	    prepStmt.executeUpdate();

	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.returnConnecction(conn);
	    silentClosePrepStmt(prepStmt);
	}
    }

    public void delete(Station station) {
	Connection conn = pool.getConnection();
	PreparedStatement prepStmt = null;

	try {

	    prepStmt = conn.prepareStatement("DELETE FROM STATION "
		    + "WHERE STATIONID = ?;");
	    prepStmt.setInt(1, station.getStationID());

	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    e.printStackTrace();
	    logger.error(e);
	} finally {
	    pool.returnConnecction(conn);
	    silentClosePrepStmt(prepStmt);
	}
    }

    public ArrayList<Station> readAllStations() {
	Connection conn = pool.getConnection();
	Statement stmt = null;
	ResultSet resSet = null;
	ArrayList<Station> res = new ArrayList<Station>();

	try {
	    stmt = conn.createStatement();
	    resSet = stmt.executeQuery("select * from STATION;");

	    while (resSet.next()) {
		res.add(new Station(resSet.getInt("STATIONID"), resSet
			.getString("NAME"), resSet.getBoolean(3), resSet
			.getBoolean(4)));
	    }

	} catch (SQLException e) {
	    e.printStackTrace();
	} finally {
	    pool.returnConnecction(conn);
	    silentCloseStmt(stmt);
	    silentCloseResSet(resSet);
	}
	return res;

    }

    public void update(Station station, String name, boolean existingRail,
	    boolean existingCable) {
	Connection conn = pool.getConnection();
	PreparedStatement prepStmt = null;

	try {
	    prepStmt = conn
		    .prepareStatement("update station set name = ?, existingRail = ? ," +
		    		" existingCable=?   where stationID = ?");
	    prepStmt.setString(1, name);
	    prepStmt.setBoolean(2, existingRail);
	    prepStmt.setBoolean(3, existingCable);
	    prepStmt.setInt(4, station.getStationID());

	    prepStmt.executeUpdate();

	} catch (SQLException e) {
	  logger.error(e);
	} finally {
	    pool.returnConnecction(conn);
	    silentClosePrepStmt(prepStmt);
	}

    }

    public void silentClosePrepStmt(PreparedStatement prepStmt) {
	System.out.println("destroying prepare statement!");
	if (prepStmt != null) {
	    try {
		prepStmt.close();
	    } catch (SQLException e) {

	    }
	}
    }

    public void silentCloseStmt(Statement stmt) {
	if (stmt != null)
	    try {
		stmt.close();
	    } catch (SQLException e) {

	    }
    }

    public void silentCloseResSet(ResultSet set) {
	System.out.println("destroying ResSet!");
	if (set != null)
	    try {
		set.close();
	    } catch (SQLException e) {
		logger.error(e);
	    }
    }

    public void deleteAll() {
	Connection conn = pool.getConnection();
	Statement stmt = null;
	try {
	    stmt = conn.createStatement();
	    stmt.executeUpdate("delete from station");

	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(conn);
	    silentCloseStmt(stmt);
	}

    }

    private int getColumnCount(ResultSet resSet){
	int size = 0;
	try {
	    while(resSet.next())
	        size++;
	} catch (SQLException e) {
	  logger.error(e);
	}
	try {
	    resSet.beforeFirst();
	} catch (SQLException e) {
	    logger.error(e);
	}
	return size;
    }
    public ArrayList<Station> readAtRoute(int routeID) {
	ArrayList<Station> toReturn = null;
	Connection con = pool.getConnection();
	PreparedStatement prepStmt = null;
	ResultSet resSet = null;
	try {
	    prepStmt = con.prepareStatement("select * from station where stationid in" +
	    		"(select stationid  from stationlist where ROUTEID =?)",
	    		ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY
		    );
	    prepStmt.setInt(1, routeID);
	    resSet = prepStmt.executeQuery();
	    toReturn = new ArrayList<Station>(getColumnCount(resSet));
	    while(resSet.next()){
		toReturn.add( new Station( resSet.getInt("stationID"), 
					resSet.getString("name"),
					resSet.getBoolean("EXISTINGRAIL"),
					resSet.getBoolean("EXISTINGCABLE")));
					
	    };
	    
	} catch (SQLException e) {
	    logger.error(e);
	}finally{
	    pool.returnConnecction(con);
	    silentClosePrepStmt(prepStmt);
	}
	
	return toReturn;
    }

    public void delete(String name) {
	Connection conn = pool.getConnection();
	PreparedStatement prepStmt = null;

	try {

	    prepStmt = conn.prepareStatement("DELETE FROM STATION "
		    + "WHERE name = ?;");
	    prepStmt.setString(1, name);

	    prepStmt.executeUpdate();
	} catch (SQLException e) {
	    logger.error(e);
	} finally {
	    pool.returnConnecction(conn);
	    silentClosePrepStmt(prepStmt);
	}	
    }

}
